<?php
require_once($_SERVER['DOCUMENT_ROOT']."/defaultconf.mex");
require_once($GLOBALS['PAD']."Aplications/ReportModels/report.vars.php");
MYTCONN();
	include($GLOBALS['OBJ'].'obj.mathgrids.mex');
	$NoResults = '<tr bgcolor="#F2F2F2"><td colspan="13">Não existem resultados para apresentação.</td></tr>';

	$FinantialResult = 0;

	if (!isset($_POST['ConsultDate'])) { $_POST['ConsultDate'] = date("Y-m-d"); }
	if(!isset($_POST['Tini'])) { $_POST['Tini'] = date("Y-m-d"); }
	if(!isset($_POST['Tend'])) { $_POST['Tend'] = $_POST['Tini']; }
	if(!isset($_POST['Origens'])) { $_POST['Origens'] = 'OrderOffice'; }
	if(!isset($_POST['Countries'])) { $_POST['Countries'] = '301,305,300'; }
	$Tini = $_POST['Tini'];
	$Tend = $_POST['Tend'];

	$ConsultDay = substr($_POST['ConsultDate'],8,2);
	$ConsultMonth = substr($_POST['ConsultDate'],5,2);
	$ConsultYear = substr($_POST['ConsultDate'],0,4);
	$RateField = $_POST['RelRate'];
	$QrCountry = " AND FraCorresponsal IN ({$_POST['Countries']})";
	$ClCountry = " AND BnfPais = 76";
	if ($_POST['Origens'] == 1) {
	$QrOrigens = 'CliOffice';
	$QrOrNAME = 'main_offices.name';
	} else {
	$QrOrigens = 'CliOffice';
	$QrOrNAME = 'OriNombre';
	}
	$currency = '';
	echo $tablehead;
	echo '<tr style="height:24px;text-align:center">
	<td colspan="13" bgcolor="#FFFFFF">Resumo de cadastros registados por lojas no periodo entre '.$Tini.' e '.$Tend.'</td></tr>
	<tr style="height:24px;text-align:center" bgcolor="#D7E3E8">
	<td width="20%">Lojas</td>
	<td width="10%">Total de Cadastros</td>
	<td width="10%">Bloqueado</td>
	<td width="10%">Com Envio</td>
	<td width="10%">Com Envio Ultimo Trimestre</td>
	<td width="10%">Sem Envio</td>
	<td width="10%">% Eficacia</td>
	<td width="10%">% Fidelizado</td>
	</tr>';
	$TotalSumWorkers = 0;

	$QResults = "SELECT
	{$QrOrigens} AS OrderOffice,
	COUNT(DISTINCT CliNumero) AS NewClients,
	COUNT(DISTINCT FraCliNumero) AS WithInvoice,
	COUNT(DISTINCT(IF(FraFechaIngreso IS NOT NULL AND FraFechaIngreso>DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 MONTH),FraCliNumero,NULL))) AS WithInvoiceLastTrimester,
	SUM(IF(CliBloqueado > 0,1,0)) AS Bloqued,
	main_offices.ref AS OfficeID,
	{$QrOrNAME} AS OfficeNAME,
	{$QrOrigens} AS OriNumero
	FROM clientes
	LEFT OUTER JOIN facturas ON FraCliNumero = CliNumero
	LEFT OUTER JOIN origenes ON OriNumero = CliOffice
	LEFT OUTER JOIN systemoffice.main_offices ON office_number = CliOffice
	WHERE CliProfile = 1 AND CliFechaAlta BETWEEN '{$Tini} 00:00:00' AND '{$Tend} 23:59:59'
	GROUP BY {$QrOrigens}
	ORDER BY {$QrOrigens}";


	$GET = $GLOBALS['MYT']->db_array($QResults);
	$Totremessas = $GET[0]+1;
if (!isset($GET[1])) {
	echo $NoResults;
} else {
	for($h = 1; $h <= $Totremessas; $h++) {
	echo '<tr bgcolor="'.gridlines($h).'" height="20"><td class="BoxSub">';
	if ($h != $Totremessas) {
  	echo $GET[$h]['OfficeNAME'];
	} else {
  	echo 'Totais: ';
	}
    echo '</td>
	<td align="right" class="BoxField">'.FieldResult($GET,$h,'NewClients').'</td>
	<td align="right" class="BoxField">'.FieldResult($GET,$h,'Bloqued').'</td>
	<td align="right" class="BoxField">'.FieldResult($GET,$h,'WithInvoice').'</td>
	<td align="right" class="BoxField">'.FieldResult($GET,$h,'WithInvoiceLastTrimester').'</td>
	<td align="right" class="BoxField">'.(FieldResult($GET,$h,'NewClients')-FieldResult($GET,$h,'WithInvoice')).'</td>
	<td align="right" class="BoxField">'.number_format((FieldResult($GET,$h,'WithInvoice')/FieldResult($GET,$h,'NewClients')*100),0).' %</td>
	<td align="right" class="BoxField">'.number_format((FieldResult($GET,$h,'WithInvoiceLastTrimester')/FieldResult($GET,$h,'NewClients')*100),0).' %</td>
	</tr>';
	}
} ?></table>